package insertDataStatic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * The class used for prepare table "days" and "times" Insert the records into 2
 * tables
 * */
public class InsertDataMain {
	public final static String SCHEMA_NAME = "DataGeo";
	public final static String DATETIMESINTERAL_TABLE_NAME = "dateTimesInterval";
	public final static String DATETIMESSPEED_TABLE_NAME = "dateTimesSpeed";
	public final static String DAYS_TABLE_NAME = "days";
	public final static String TIMES_TABLE_NAME = "times";
	static String days[] = { "Monday", "Tuesday", "Wednesday", "Thursday",
			"Friday", "Saturday", "Sunday" };

	/**
	 * @param args
	 * @throws SQLException
	 */
	public static void main(String[] args) throws SQLException {
		Connection connection = null;

		System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");

		try {

			Class.forName("org.postgresql.Driver");

		} catch (ClassNotFoundException e) {

			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;

		}

		System.out.println("PostgreSQL JDBC Driver Registered!");

		try {

			connection = DriverManager.getConnection(
					"jdbc:postgresql://203.185.65.41:5432/Traffic_Data",
					"fang", "root");

		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;

		}

		if (connection != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}

		Statement stmt = connection.createStatement();

		// ******************************************************************

		// InsertDays(stmt);
		// InsertTimes(TimeString(), stmt);
		InsertDateTimeInterval(stmt, TimeString(), days);
		InsertDateTimesSpeed(stmt, TimeStringHour(), days);
	}

	// -------------------------------------------------------------------------------
	public static void InsertDateTimesSpeed(Statement stmt,
			ArrayList<String> timeStringHour, String[] days)
			throws SQLException {
		for (String day : days) {
			for (String time : timeStringHour) {
				String queryStr = String
						.format("Insert into \"%s\".\"%s\" (\"daysSpeed\",\"timesSpeed\") VALUES (\'%s\',\'%s\')",
								SCHEMA_NAME, DATETIMESSPEED_TABLE_NAME, day.toLowerCase(),
								time);
				stmt.executeUpdate(queryStr);
			}
		}
		System.out.println("Insert into " + DATETIMESSPEED_TABLE_NAME
				+ " is completed");

	}

	// -------------------------------------------------------------------------------
	public static void InsertDateTimeInterval(Statement stmt,
			ArrayList<String> timeString, String[] days) throws SQLException {

		for (String day : days) {
			for (String time : timeString) {
				String queryStr = String
						.format("Insert into \"%s\".\"%s\" (\"days\",\"timesInterval\") VALUES (\'%s\',\'%s\')",
								SCHEMA_NAME, DATETIMESINTERAL_TABLE_NAME, day.toLowerCase(),
								time);
				stmt.executeUpdate(queryStr);
			}
		}
		System.out.println("Insert into " + DATETIMESINTERAL_TABLE_NAME
				+ " is completed");
	}

	// -------------------------------------------------------------------------------
	// 5 intervals
	public static ArrayList<String> TimeString() {
		ArrayList<String> timesStr = new ArrayList<String>();
		int interval_min = 5;

		int hour = 00;
		int min = 00;
		String hourStr = "00";
		String minuteStr = "00";

		while (hour < 24) {

			if (min < 60) {
				if (min < 10) {
					minuteStr = "0" + min;
				} else {
					minuteStr = "" + min;
				}
			} else if (min == 60) {
				min = 0;
				hour = hour + 1;
				if (hour < 10) {
					hourStr = "0" + hour;
				} else {
					hourStr = "" + hour;
				}
				minuteStr = "0" + min;
			}
			min = min + interval_min;
			String timeStr = String.format("%s:%s:00", hourStr, minuteStr);
			timesStr.add(timeStr);
		}
		return timesStr;

	}

	// -------------------------------------------------------------------------------
	// 5 intervals
	public static ArrayList<String> TimeStringHour() {
		ArrayList<String> timesStr = new ArrayList<String>();
		int interval_hour = 1;

		int hour = 00;
		int min = 00;
		String hourStr = "00";
		String minuteStr = "00";

		while (hour < 25) {
			if (hour < 10) {
				hourStr = "0" + hour;
			} else {
				hourStr = "" + hour;
			}
			hour = hour + interval_hour;
			String timeStr = String.format("%s:%s:00", hourStr, minuteStr);
			// System.out.println(timeStr);
			timesStr.add(timeStr);
		}
		return timesStr;
	}

	// -------------------------------------------------------------------------------
	// ===============================[CLOSED]=========================================
	// public static void InsertTimes(ArrayList<String> timeString, Statement
	// stmt)
	// throws SQLException {
	// for (String time : timeString) {
	// String queryStr = String.format(
	// "Insert into \"%s\".\"%s\" (\"timeName\") VALUES (\'%s\')",
	// SCHEMA_NAME, TIMES_TABLE_NAME, time);
	// // System.out.println(queryStr);
	// stmt.executeUpdate(queryStr);
	// }
	//
	// }
	//
	// public static void InsertDays(Statement stmt) throws SQLException {
	// // TODO Auto-generated method stub
	//
	// for (String day : days) {
	// String queryStr = String.format(
	// "Insert into \"%s\".\"%s\" (\"dayName\") VALUES (\'%s\')",
	// SCHEMA_NAME, DAYS_TABLE_NAME, day);
	// // System.out.println(queryStr);
	// stmt.executeUpdate(queryStr);
	//
	// }
	//
	// }
	// ===============================[CLOSED]=========================================
}
